﻿namespace Hidistro.SqlDal.Comments
{
    using Hidistro.Core;
    using Hidistro.Core.Entities;
    using Hidistro.Core.Enums;
    using Hidistro.Entities;
    using Hidistro.Entities.Comments;
    using Hidistro.Membership.Context;
    using Hidistro.Membership.Core.Enums;
    using Microsoft.Practices.EnterpriseLibrary.Data;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Globalization;
    using System.Text;

    public class MessageBoxDao
    {
        private Database database = DatabaseFactory.CreateDatabase();

        public bool AddMessage(MessageBoxInfo messageBoxInfo, UserRole toRole)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("BEGIN TRAN ");
            builder.Append("DECLARE @ContentId int ");
            builder.Append("DECLARE @errorSun INT ");
            builder.Append("SET @errorSun=0 ");
            builder.Append("INSERT INTO [Hishop_MessageContent]([Title],[Content],[Date]) ");
            builder.Append("VALUES(@Title,@Content,@Date) ");
            builder.Append("SET @ContentId = @@IDENTITY  ");
            builder.Append("SET @errorSun=@errorSun+@@ERROR  ");
            builder.AppendFormat("INSERT INTO [{0}]([ContentId],[Sernder],[Accepter],[IsRead]) ", "Hishop_MemberMessageBox");
            builder.Append("VALUES(@ContentId,@Sernder ,@Accepter,@IsRead) ");
            builder.Append("SET @errorSun=@errorSun+@@ERROR  ");
            builder.Append("IF @errorSun<>0 ");
            builder.Append("BEGIN ");
            builder.Append("ROLLBACK TRANSACTION  ");
            builder.Append("END ");
            builder.Append("ELSE ");
            builder.Append("BEGIN ");
            builder.Append("COMMIT TRANSACTION  ");
            builder.Append("END ");
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(builder.ToString());
            this.database.AddInParameter(sqlStringCommand, "Title", DbType.String, messageBoxInfo.Title);
            this.database.AddInParameter(sqlStringCommand, "Content", DbType.String, messageBoxInfo.Content);
            this.database.AddInParameter(sqlStringCommand, "Date", DbType.DateTime, DataHelper.GetSafeDateTimeFormat(DateTime.Now));
            this.database.AddInParameter(sqlStringCommand, "Sernder", DbType.String, messageBoxInfo.Sernder);
            this.database.AddInParameter(sqlStringCommand, "Accepter", DbType.String, messageBoxInfo.Accepter);
            this.database.AddInParameter(sqlStringCommand, "IsRead", DbType.Boolean, messageBoxInfo.IsRead);
            return (this.database.ExecuteNonQuery(sqlStringCommand) > 0);
        }

        public int DeleteManagerMessages(IList<long> messageList)
        {
            string str = string.Empty;
            foreach (long num in messageList)
            {
                if (string.IsNullOrEmpty(str))
                {
                    str = str + num.ToString(CultureInfo.InvariantCulture);
                }
                else
                {
                    str = str + "," + num.ToString(CultureInfo.InvariantCulture);
                }
            }
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(string.Format("delete from Hishop_ManagerMessageBox where MessageId in ({0}) ", str));
            return this.database.ExecuteNonQuery(sqlStringCommand);
        }

        public MessageBoxInfo GetManagerMessage(long messageId)
        {
            MessageBoxInfo info = null;
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM vw_Hishop_ManagerMessageBox WHERE MessageId=@MessageId;");
            this.database.AddInParameter(sqlStringCommand, "MessageId", DbType.Int64, messageId);
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                if (reader.Read())
                {
                    info = DataMapper.PopulateMessageBox(reader);
                }
            }
            return info;
        }

        public DbQueryResult GetManagerReceivedMessages(MessageBoxQuery query, UserRole role)
        {
            string filter = string.Format("Accepter='{0}' AND Sernder IN (SELECT UserName FROM aspnet_Users WHERE UserRole = {1})", query.Accepter, (int) role);
            if (query.MessageStatus == MessageStatus.Replied)
            {
                filter = filter + " AND IsRead = 1";
            }
            if (query.MessageStatus == MessageStatus.NoReply)
            {
                filter = filter + " AND IsRead = 0";
            }
            return DataHelper.PagingByRownumber(query.PageIndex, query.PageSize, "MessageId", SortAction.Desc, query.IsCount, "vw_Hishop_ManagerMessageBox", "MessageId", filter, "*");
        }

        public DbQueryResult GetManagerSendedMessages(MessageBoxQuery query, UserRole role)
        {
            string filter = string.Format("Sernder='{0}' AND Accepter IN (SELECT UserName FROM aspnet_Users WHERE UserRole = {1})", query.Sernder, (int) role);
            return DataHelper.PagingByRownumber(query.PageIndex, query.PageSize, "MessageId", SortAction.Desc, query.IsCount, "vw_Hishop_ManagerMessageBox", "MessageId", filter, "*");
        }

        public IList<Member> GetMembersByRank(int? gradeId)
        {
            DbCommand sqlStringCommand;
            IList<Member> list = new List<Member>();
            if (gradeId > 0)
            {
                sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM vw_aspnet_Members WHERE GradeId=@GradeId");
                this.database.AddInParameter(sqlStringCommand, "GradeId", DbType.Int32, gradeId);
            }
            else
            {
                sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM vw_aspnet_Members");
            }
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                while (reader.Read())
                {
                    Member item = new Member(UserRole.Member) {
                        UserId = (int) reader["UserId"],
                        Email = reader["Email"].ToString(),
                        Username = reader["UserName"].ToString()
                    };
                    list.Add(item);
                }
            }
            return list;
        }

        public int GetMemberUnReadMessageNum()
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT ISNULL(COUNT(*),0) FROM Hishop_MemberMessageBox WHERE IsRead=0 and Accepter=@Accepter");
            this.database.AddInParameter(sqlStringCommand, "Accepter", DbType.String, HiContext.Current.User.Username);
            return (int) this.database.ExecuteScalar(sqlStringCommand);
        }

        public bool InsertMessage(MessageBoxInfo messageBoxInfo, UserRole toRole)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("BEGIN TRAN ");
            builder.Append("DECLARE @ContentId int ");
            builder.Append("DECLARE @errorSun INT ");
            builder.Append("SET @errorSun=0 ");
            builder.Append("INSERT INTO [Hishop_MessageContent]([Title],[Content],[Date]) ");
            builder.Append("VALUES(@Title,@Content,@Date) ");
            builder.Append("SET @ContentId = @@IDENTITY  ");
            builder.Append("SET @errorSun=@errorSun+@@ERROR  ");
            builder.Append("INSERT INTO [Hishop_ManagerMessageBox]([ContentId],[Sernder],[Accepter],[IsRead]) ");
            builder.Append("VALUES(@ContentId,@Sernder ,@Accepter,@IsRead) ");
            builder.Append("SET @errorSun=@errorSun+@@ERROR  ");
            builder.AppendFormat("INSERT INTO [{0}]([ContentId],[Sernder],[Accepter],[IsRead]) ", "Hishop_MemberMessageBox");
            builder.Append("VALUES(@ContentId,@Sernder ,@Accepter,@IsRead) ");
            builder.Append("SET @errorSun=@errorSun+@@ERROR  ");
            builder.Append("IF @errorSun<>0 ");
            builder.Append("BEGIN ");
            builder.Append("ROLLBACK TRANSACTION  ");
            builder.Append("END ");
            builder.Append("ELSE ");
            builder.Append("BEGIN ");
            builder.Append("COMMIT TRANSACTION  ");
            builder.Append("END ");
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(builder.ToString());
            this.database.AddInParameter(sqlStringCommand, "Title", DbType.String, messageBoxInfo.Title);
            this.database.AddInParameter(sqlStringCommand, "Content", DbType.String, messageBoxInfo.Content);
            this.database.AddInParameter(sqlStringCommand, "Date", DbType.DateTime, DataHelper.GetSafeDateTimeFormat(DateTime.Now));
            this.database.AddInParameter(sqlStringCommand, "Sernder", DbType.String, messageBoxInfo.Sernder);
            this.database.AddInParameter(sqlStringCommand, "Accepter", DbType.String, messageBoxInfo.Accepter);
            this.database.AddInParameter(sqlStringCommand, "IsRead", DbType.Boolean, messageBoxInfo.IsRead);
            return (this.database.ExecuteNonQuery(sqlStringCommand) > 0);
        }

        public bool PostManagerMessageIsRead(long messageId)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("Update Hishop_ManagerMessageBox set IsRead=1 where MessageId=@MessageId");
            this.database.AddInParameter(sqlStringCommand, "MessageId", DbType.Int64, messageId);
            return (this.database.ExecuteNonQuery(sqlStringCommand) > 0);
        }
    }
}

